package  ybt.demo.service;

import java.util.List;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import  ybt.demo.custom.BaseService;
import  ybt.demo.entity.Course;
import  ybt.demo.entity.Grade;
import  ybt.demo.entity.Student;
import  ybt.demo.repository.GradeRepository;
import  ybt.demo.repository.StudentRepository;


@Service
public class GradeService extends BaseService<Grade, Integer>{
	@Resource
	private EntityManager entityManager;
	@Autowired
	private GradeRepository gradeService;
	@Autowired
	private StudentRepository studentService;
	@Autowired
	private CourseService courseService;

	
	

	public Grade findByStudentIdAndCourseId(Integer id, Integer kcId) {
		return gradeService.findByStudentIdAndCourseId(id, kcId);

}
  
	public List<List<Object>> findAllGradeByTbClassId(List<Course> courses , Integer bjId){
		                      
		String hql=" select t.student.name ";
		
		for (Course course : courses) {
			hql+=",sum(case when t.course.id = "+course.getId()+" then t.grade else 0 end)";			
		}
		hql+=",sum(t.grade) as total";
		hql+=" from Grade t";
		hql+=" where t.student.tbClass.id="+bjId;
		hql+=" group by t.student.id";
		hql+=" order by total desc";
		Query query = entityManager.createQuery(hql);		
		return query.getResultList();

	}


		public List<Grade> cjbfindAllbjidAndkcid(Integer bjid, Integer kcid) {
		List<Student> list=studentService.findByTbClassId(bjid);
		Course course = courseService.findById(kcid);
		List<Grade> grades =gradeService.findByStudent_TbClass_IdAndCourseId(bjid,kcid);		
		if(list.size()>grades.size()) {
		for(Student student:list) {
			   Grade g=new Grade();
			   Grade gg = gradeService.findByStudentIdAndCourseId(student.getId(),kcid);
			   if(gg==null) {
				g.setStudent(student);
				g.setCourse(course);
				gradeService.save(g);
				grades.add(g);
			   }else {
				   grades.add(gg);
			   }
			}
		}
		return grades;
	}
		public List<Grade> findByStudentId(Integer id) {
			List<Grade> grades=gradeService.findByStudentId(id);
			return grades;
		}
}